<CFSET dDate = DATEFORMAT(Now(),"mm/dd/yyyy")> 
<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#dDate#">
<CFSET sDate1 = dateResult>

<CFSET sMessPostApproval="Employee's Work Schedule was successfully changed.">
<CFQUERY NAME="qryECINCHANGEWS" DATASOURCE="#client.company_dsn#">
	SELECT *
	FROM ECINCHANGEWS
	WHERE ROUTEID = '#GetRecord.ROUTEID#'
</CFQUERY>

<CFIF qryECINCHANGEWS.RecordCount>
	<CFTRANSACTION>
	<CFTRY>
		<CFSET sNotPosted="">

		<!--- Maximum of 100 employees for batch ot --->
		<CFLOOP INDEX="x" FROM="1" TO="100">
			<CFIF IsDefined("qryECINCHANGEWS.PERSONNELIDNO#x#") AND Evaluate("qryECINCHANGEWS.PERSONNELIDNO" & x) NEQ "">
				<CFSET sPIN=Evaluate("qryECINCHANGEWS.PERSONNELIDNO" & x)>
				<CFSET sWorkSkedCode=Evaluate("qryECINCHANGEWS.WORKSKEDCODE" & x)>
				<CFSET dReferenceDate=Evaluate("qryECINCHANGEWS.EFFECTIVEDATE" & x)>
				<CFSET dEndDate=DateAdd("d",-1,#dReferenceDate#)>

				<CFQUERY NAME="GetSchedule" DATASOURCE=#client.company_dsn#>
					SELECT * FROM CINWSMASTER
					WHERE PERSONNELIDNO = '#sPIN#'
					AND ACTIVESKED = 'Y'
				</CFQUERY>

				<CFSET sPrevSked="#GetSchedule.WORKSKEDCODE#">
				<CFSET sPrevStart="#GetSchedule.STARTDATE#">


				<CFIF IsDate(dReferenceDate) AND
					  IsNumericDate(dReferenceDate) AND
					  sWorkSkedCode NEQ "">
					<CFQUERY NAME="qrySkedMaster" DATASOURCE=#client.company_dsn#>
						SELECT *
						FROM CINWSMASTER
						WHERE PERSONNELIDNO = '#sPIN#' AND
							  STARTDATE = '#dReferenceDate#' AND
							  WORKSKEDCODE = '#sWorkSkedCode#'
					</CFQUERY>

					<CFQUERY NAME="UpdatePrevious" DATASOURCE=#client.company_dsn#>
							UPDATE CINWSMASTER
							SET ACTIVESKED = 'N'
							WHERE PERSONNELIDNO = '#sPIN#'
					</CFQUERY>

					<CFQUERY NAME="GetCategory" DATASOURCE=#client.company_dsn#>
						    SELECT CATEGORY
							FROM CLKWORKSKED
							WHERE WORKSKEDCODE = '#sWorkSkedCode#'
					</CFQUERY>

					<CFIF NOT qrySkedMaster.RecordCount>
						<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#dReferenceDate#">
						<CFSET sDate2 = dateResult>
						  <CFQUERY NAME="qryInsert" DATASOURCE=#client.company_dsn#>
							INSERT INTO CINWSMASTER
								(PERSONNELIDNO,
								STARTDATE,
								WORKSKEDCODE,
								CATEGORY,
								ACTIVESKED,
								RECCREATEDBY,
								RECDATECREATED,
								USERID,
								DATELASTUPDATE,
								TIMELASTUPDATE)
							VALUES
								('#sPIN#',
								#preserveSingleQuotes(sDate2)#,
								'#sWorkSkedCode#',
								'#GetCategory.CATEGORY#',
								'Y',
								'#client.userid#',
								#preserveSingleQuotes(sDate1)#,
								'#client.userid#',
								#preserveSingleQuotes(sDate1)#,
								'#TimeFormat(Now(), "HH:mm:ss")#')
						</CFQUERY>
					<CFELSE>
						<CFQUERY NAME="qryUpdate" DATASOURCE=#client.company_dsn#>
							UPDATE CINWSMASTER
							SET ACTIVESKED = 'Y'
							WHERE PERSONNELIDNO = '#sPIN#' AND
								  STARTDATE = '#dReferenceDate#'
						</CFQUERY>
					</CFIF>
				</CFIF>

				<CFIF IsDate(dReferenceDate) AND
					  IsNumericDate(dReferenceDate) AND
					  IsDate(dEndDate) AND
					  IsNumericDate(dEndDate) AND
					  sWorkSkedCode NEQ "">
					<CFQUERY NAME="qryTempSkedExist" DATASOURCE=#client.company_dsn#>
						SELECT PERSONNELIDNO
						FROM CINWSTEMPSKED
						WHERE PERSONNELIDNO = '#sPIN#' AND
							  STARTDATE = '#dReferenceDate#'
					</CFQUERY>

					<CFIF NOT qryTempSkedExist.RecordCount>
						<!--- check if new date range will overlap to an existing sked --->
						<CFQUERY NAME="qryAllSked" DATASOURCE=#client.company_dsn#>
							SELECT STARTDATE, ENDDATE
							FROM CINWSTEMPSKED
							WHERE PERSONNELIDNO = '#sPIN#'
						</CFQUERY>

						<CFSET lBetween=false>

						<CFLOOP QUERY="qryAllSked">
							<CFMODULE TEMPLATE="#client.common_path#cfm_inbetweendays.cfm"
								FROMDATE="#dReferenceDate#"
								TODATE="#dEndDate#"
								COMPDATE="#qryAllSked.STARTDATE#">

							<CFIF INBETWEENDAYS>
								<CFSET lBetween=true>
								<CFBREAK>
							<CFELSE>
								<CFMODULE TEMPLATE="#client.common_path#cfm_inbetweendays.cfm"
								FROMDATE="#dReferenceDate#"
								TODATE="#dEndDate#"
								COMPDATE="#qryAllSked.ENDDATE#">

								<CFIF INBETWEENDAYS>
									<CFSET lBetween=true>
									<CFBREAK>
								<CFELSE>
									<CFMODULE TEMPLATE="#client.common_path#cfm_inbetweendays.cfm"
									FROMDATE="#qryAllSked.STARTDATE#"
									TODATE="#qryAllSked.ENDDATE#"
									COMPDATE="#dReferencedate#">

									<CFIF INBETWEENDAYS>
										<CFSET lBetween=true>
										<CFBREAK>
									<CFELSE>
										<CFMODULE TEMPLATE="#client.common_path#cfm_inbetweendays.cfm"
										FROMDATE="#qryAllSked.STARTDATE#"
										TODATE="#qryAllSked.ENDDATE#"
										COMPDATE="#dEndDate#">

										<CFIF INBETWEENDAYS>
											<CFSET lBetween=true>
											<CFBREAK>
										</CFIF>
									</CFIF>
								</CFIF>
							</CFIF>
						</CFLOOP> <!--- loop all defined temp worksked --->

						<CFIF NOT lBetween>
							<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#sPrevStart#">
							<CFSET sDate3 = dateResult>
							<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#dEndDate#">
							<CFSET sDate4 = dateResult>
							
							<CFQUERY NAME="qryInsert" DATASOURCE=#client.company_dsn#>
								INSERT INTO CINWSTEMPSKED
									(PERSONNELIDNO,
									STARTDATE,
									ENDDATE,
									WORKSKEDCODE,
									RECCREATEDBY,
									RECDATECREATED,
									USERID,
									DATELASTUPDATE,
									TIMELASTUPDATE)
								VALUES
									('#sPIN#',
									#preserveSingleQuotes(sDate3)#,
									#preserveSingleQuotes(sDate4)#,
									'#sPrevSked#',
									'#client.userid#',
									#preserveSingleQuotes(sDate1)#,
									'#client.userid#',
									#preserveSingleQuotes(sDate1)#,
									'#TimeFormat(Now(), "HH:mm:ss")#')
							</CFQUERY>
						<CFELSE>
							<CFQUERY NAME="qryCMFPA" DATASOURCE=#client.company_dsn#>
								SELECT LASTNAME, MIDDLENAME, FIRSTNAME
								FROM CMFPA
								WHERE PERSONNELIDNO = '#sPin#'
							</CFQUERY>

							<CFSET sNotPosted=sNotPosted & "&nbsp;&nbsp;&nbsp;" & sPin & " (#qryCMFPA.FIRSTNAME# #qryCMFPA.MIDDLENAME# #qryCMFPA.LASTNAME#)<BR>">
						</CFIF>
					<CFELSE>
						<CFQUERY NAME="qryUpdate" DATASOURCE=#client.company_dsn#>
							UPDATE CINWSTEMPSKED
							SET WORKSKEDCODE = '#sPrevSked#'
							WHERE PERSONNELIDNO = '#sPIN#' AND
							  	  STARTDATE = '#dReferenceDate#' AND
							  	  ENDDATE = '#dEndDate#'
						</CFQUERY>
					</CFIF>
				</CFIF>
			</CFIF>
		</CFLOOP>

		<CFTRANSACTION ACTION="COMMIT"/>
		<CFCATCH TYPE="Database">
			<CFTRANSACTION ACTION="ROLLBACK"/>
			<CFSET sNotPosted="">
			<CFSET sMessPostApproval="Error in post-approval activity ...<br><br>#CFCATCH.message# #CFCATCH.DETAIL#">
		</CFCATCH>
	</CFTRY>
	</CFTRANSACTION>

	<CFIF sNotPosted NEQ "">
		<CFSET sMessPostApproval=sMessPostApproval & " Except for the following employees ...<BR>" & sNotPosted>
	</CFIF>
<CFELSE>
	<CFSET sMessPostApproval="WARNING!!! Work Schedule was not changed. Application form is missing.<BR>Please contact your administrator.">
</CFIF>